********************************************************
******Summary Stats of Importers and Affiliates*****
********************************************************

/*

Written by TF on 5/17/2023

making a simple table of import types by mne status to disclose

*/


 **Set directories
cd "xxxx"  /* PROJECT ROOT FOLDER */

/* DEFINE GLOBALS PATHS HERE */
global data "data"
global output "output"
global input "data"
global disc_code "code"
global disc_programs "code"





use $data/country_imp_aff2007_type_02.dta, clear  // this dataset is firms with some imports or MNE 



*************************************************
*1. Prepare data for tables
************************************************
replace mne_status="FOR" if mne_status=="FO2"
replace mne_status="DOM" if mne_status==""


gen al_imp=imp_value-rp_imp_value
gen al_imp_input=imp_input-rp_imp_input 

*make AL imports
foreach vv in bothf prodf_nif inputf_np nonef {
	gen al_imp_`vv'=imp_`vv'-rp_imp_`vv'
	}
	

gen low_income=1 if gdp_pc_qtile<=2
  replace low_income=0 if gdp_pc_qtile>2 & gdp_pc_qtile~=.
  
save "$data/imp_int_regs_02.dta", replace
***************************************************************************


*see _08 version of this program for regression code using these variables*


************************************************* 
*3.  Summary Statistics on Import Types******
*************************************************
*Calculate import shares by type and mne status
***********************************
use "$data/imp_int_regs_02.dta", clear
  collapse (sum) imp_value al_imp al_imp_bothf al_imp_prodf_nif al_imp_inputf_np al_imp_nonef ///
		 rp_imp_value rp_imp_nonef rp_imp_inputf_np rp_imp_prodf_nif rp_imp_bothf imp_tot_type rp_imp_tot_type, by(mne_status) fast
		
		
		
label variable rp_imp_bothf "Firm reports input and production"
label variable rp_imp_nonef "Firm does not report input or production"
label variable rp_imp_inputf_np "Firm reports input but not production"
label variable rp_imp_prodf_nif "Firm reports production but not input"

label variable al_imp_bothf "Firm reports input and production"
label variable al_imp_nonef "Firm does not report input or production"
label variable al_imp_inputf_np "Firm reports input but not production"
label variable al_imp_prodf_nif "Firm reports production but not input"

rename rp_imp_value rp_imp


*Check totals
gen check_rp_imp=rp_imp-rp_imp_tot_type
summ check

foreach vv in al rp {
	egen check_tot_`vv'=rowtotal(`vv'_imp_bothf `vv'_imp_prodf_nif `vv'_imp_inputf_np `vv'_imp_nonef)
	gen check_`vv'=`vv'_imp-check_tot_`vv'	
	}
	


*Make a table to disclose with the total AL and RP flows by type
foreach vv in al_imp_bothf al_imp_prodf_nif al_imp_inputf_np al_imp_nonef rp_imp_nonef rp_imp_inputf_np rp_imp_prodf_nif rp_imp_bothf {
	replace `vv'=`vv'/1000
	}
	


*Round for DRB rules:
  do $disc_programs/rounding_4sigdig_v2.do  al_imp_bothf al_imp_prodf_nif al_imp_inputf_np al_imp_nonef rp_imp_nonef rp_imp_inputf_np rp_imp_prodf_nif rp_imp_bothf

format al* rp* %9.0fc
  
*Output for disclosure
export excel mne_status al_imp_bothf al_imp_prodf_nif al_imp_inputf_np al_imp_nonef rp_imp_nonef rp_imp_inputf_np rp_imp_prodf_nif rp_imp_bothf ///
    using $output/afft_restat_01.xls, sheetreplace sheet(import_flow_types) ///
    firstrow(variables)	keepcellfmt  
    
*Check disclosed output 
import excel  using $output/afft_restat_01.xls, sheet(import_flow_types) firstrow clear	

  egen total= rowtotal(al_imp_bothf al_imp_prodf_nif al_imp_inputf_np al_imp_nonef rp_imp_nonef rp_imp_inputf_np rp_imp_prodf_nif rp_imp_bothf)
  egen all_imports=sum(total)
    
*calculate shares
drop total all_imports
reshape long al_imp_ rp_imp_, i(mne_status) j(type) string

gen total=al+rp

foreach vv in al rp total {
	bys mne_status: egen tot_`vv'=sum(`vv')
	gen double sh_`vv'=round(`vv'/tot_`vv',.01)
	}

*


